package com.mysimpatico.sqlwrapper;

import java.io.File;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.CharacterIterator;
import java.text.SimpleDateFormat;
import java.text.StringCharacterIterator;
import java.util.Arrays;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Set;

/**
 * 
 * @author simpatico
 */
public final class SqlWrapper {

    /**
     *
     * @author Gabriele Kahlout
     * Not supported in Java DB: TEXT, BOOLEAN, DATETIME, BYTE,
     * Not supported in SQLite: SMALLINT, DATE, BYTE, TYNINT, FLOAT, CHAR, VARCHAR
     */
    public enum Type {

        TEXT, INT, BYTE, TINYINT, SMALLINT, BOOLEAN, DATETIME, DATE, DOUBLE, FLOAT, REAL, CHAR, VARCHAR, BLOB, NUMERIC, SERIAL, TIMESTAMP
    }

    public enum Reserved {

        group, GROUP
    }

    public enum Sort {

        ASC, DESC
    }

    public enum Condition {

        AND, OR
    }

    public enum QueryType {

        SELECT, CREATE, JOIN, TABLES, INSERT, FOREIGN, PRIMARY, UPDATE
    }

    public enum ConflictResolution {

        ROLLBACK, ABORT, FAIL, IGNORE, REPLACE
    }

    public enum setOperators {

        INTERSECT, UNION, EXCEPT
    }

    public enum vendor {

        SQLITE, JAVADB, POSTGRES
    }

    public enum SelectOption {

        DISTINCT, MIN, MAX, NONE, COUNT
    }
    public static final String TRUE = Boolean.toString(true);
    public static final String NULL = "null";
    public static final String FALSE = Boolean.toString(false);
    public static final Type TEXT = Type.TEXT;
    public static final Type INT = Type.INT;
    public static final Type BOOL = Type.BOOLEAN;
    public static final Type DATE = Type.DATE;
    static vendor db = vendor.POSTGRES;
//    public static final Type DATETIME = (db == vendor.POSTGRES)? Type.TIMESTAMP : Type.DATETIME;
    public static final Type CHAR = Type.CHAR;
    public static final Type FLOAT = Type.FLOAT;
    public static final Type REAL = Type.REAL;
    public static final Type DOUBLE = Type.DOUBLE;
    public static final Type NUM = Type.NUMERIC;
    public static final Type BLOB = Type.BLOB;
    public static final Type VARCHAR = Type.VARCHAR;
    public static final Sort ASC = Sort.ASC;
    public static final Sort DESC = Sort.DESC;
    public static final String TIMESTAMP = "CURRENT_TIMESTAMP";
    public static final int index = 1;
    public static final String rid = "rid";
    public static Column ROWID = new Column(rid);
    private static final LinkedList<String> history = new LinkedList<String>();
    public static final Column all = new Column("*");
    public static final String FULL_DATE_PATTERN = "yyyy-MM-dd HH:mm:ss";

    public static void setVendor(vendor newVendor) {
        db = newVendor;
    }

    private SqlWrapper() {
    }

    ;

    /**
     * Connects to the SQLite database named db.sqlite in the working directory. Requires sqlitejbdc library.
     * @return Java.sql.Statement for executing queries.
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static Connection connectToSQLite() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
        Class.forName("org.sqlite.JDBC");
        final Connection con = DriverManager.getConnection("jdbc:sqlite:db.sqlwrapper");
        return con;
    }

    public static Connection connectToSQLite(final String path) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
        Class.forName("org.sqlite.JDBC");
        final Connection con = DriverManager.getConnection("jdbc:sqlite:" + path + File.separator + "db.sqlwrapper");
        return con;
    }

    public static Connection connectToSQLite(final boolean create) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
        Class.forName("org.sqlite.JDBC");
        final Connection con = DriverManager.getConnection("jdbc:sqlite:db.sqlwrapper;create=" + create);
        return con;
    }

    public static Connection connectToSQLite(final String path, final boolean create) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
        Class.forName("org.sqlite.JDBC");
        final Connection con = DriverManager.getConnection("jdbc:sqlite: " + path + File.separator + "db.sqlwrapper;create=" + create);
        return con;
    }

    public static boolean shutDownDerby() {
        try {
            Connection con = connectToDerby();
            con.close();
            con = DriverManager.getConnection("jdbc:derby:db.sqlwrapper;shutdown=true");

        } catch (SQLException e) { //Derby will throw an exception on successful deletion.
            return true;
        }
        return false;
    }

    public static Connection connectToDerby() throws SQLException {
        final Connection con = DriverManager.getConnection("jdbc:derby:db.sqlwrapper;create=" + false);
        return con;
    }

    public static Connection connectToInMemoryDerby() throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
        final Connection con = DriverManager.getConnection("jdbc:derby:memory:db.sqlwrapper;create=" + false);
        return con;
    }

    public static Connection connectToInMemoryDerby(final boolean create) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
        final Connection con = DriverManager.getConnection("jdbc:derby:memory:db.sqlwrapper;create=" + create);
        return con;
    }

    public static Connection connectToDerby(final boolean create) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
        final Connection con = DriverManager.getConnection("jdbc:derby:db.sqlwrapper;create=" + create);
        return con;
    }

    public static Connection connectToDerby(final String parentPath) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
        final Connection con = DriverManager.getConnection("jdbc:derby:" + parentPath + File.separator + "db.sqlwrapper;create=" + false);
        return con;
    }

    public static Connection connectToDerby(final File parentFolder) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
        final Connection con = DriverManager.getConnection("jdbc:derby:" + parentFolder.getAbsolutePath() + File.separator + "db.sqlwrapper;create=" + false);
        return con;
    }

    public static Connection connectToDerby(final String parentPath, final boolean create) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
        //  Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        final Connection con = DriverManager.getConnection("jdbc:derby:" + parentPath + File.separator + "db.sqlwrapper;create=" + create);
        return con;
    }

    public static Connection connectToDerby(final File parentPath, final boolean create) throws InstantiationException, IllegalAccessException, ClassNotFoundException, SQLException {
        //  Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        final Connection con = DriverManager.getConnection("jdbc:derby:" + parentPath.getAbsolutePath() + File.separator + "db.sqlwrapper;create=" + create);
        return con;
    }

    public static Connection getConnection(final boolean autoCommit, final File parentFile) throws Exception {
        Connection con = null;
        try {
            con = DriverManager.getConnection("jdbc:default:connection");
        } catch (Exception e) {
            con = SqlWrapper.connectToDerby(parentFile);
            con.setAutoCommit(autoCommit);
        }
        return con;
    }

    /*
     * things that get here shouldn't be null.
     */
    public static String escapeString(String s) {
        final StringBuilder result = new StringBuilder();

        final StringCharacterIterator iterator = new StringCharacterIterator(s);
        char character = iterator.current();
        while (character != CharacterIterator.DONE) {
            if (character == '\\') {
                result.append("\\\\");
            } else if (character == '\'') {
                result.append("''");
            } else {
                result.append(character);
            }
            character = iterator.next();
        }
        return result.toString();
    }

    private static String joinStrings(final String[] strings, final Condition[] conditions) {
        String joinedStrings = "";
        for (int i = 0; i < strings.length - 1; i++) {
            String string = (strings[i] == null) ? NULL : strings[i] + " ";
            string += (conditions == null) ? " ," : conditions[i].toString() + " ";

//            joinedStrings = joinedStrings.concat((conditions == null) ? "," + string : " " + conditions[i - 1] + " " + string);
            joinedStrings += string;
        }
        final int last = strings.length - 1;
        joinedStrings += (strings[last] == null) ? NULL : strings[last];
        return joinedStrings;
    }

    private static String joinTables(final SelectableFrom[] tables) {
        final String[] strings = new String[tables.length];
        if (tables.length == 1 || tables[0].equals(tables[1])) {
            return tables[0].getName();
        }
        for (int i = 0; i < strings.length; i++) {
            strings[i] = tables[i].getName();
        }
        return joinStrings(strings, null);
    }

    private static String joinColumnsTogether(final SelectableFrom[] tables, final Column[] columns) {
        return joinColumnsTogether(tables, columns, true);
    }

    private static String joinColumnsTogether(final SelectableFrom[] tables, final Column[] columns, boolean qualifiedColumnName) {
        if (qualifiedColumnName) {
            return joinStrings(joinColumns(tables, columns), null);
        }

        final String[] strings = new String[columns.length];
        for (int i = 0; i < strings.length; i++) {
            strings[i] = columns[i].getName();
        }
        return joinStrings(strings, null);
    }

    private static String[] joinColumns(final SelectableFrom[] tables, final Column[] columns) {
        final String[] strings = new String[columns.length];
        int j = 0;
        for (int i = 0; i < strings.length; i++) {
            strings[i] = joinColumn(tables[j], columns[i]);
            if (tables.length > j + 1) {
                j++;
            }
        }
        return strings;
    }

    private static String joinColumn(final SelectableFrom table, final Column column) {
        return table.getName() + "." + column.getName();
    }

    static String joinColumns(final Column[] columns, QueryType qt) {
        if (columns == null) {
            return "";
        }
        Condition[] conds = null;
        final String[] strings = new String[columns.length];
        switch (qt) {
            case CREATE:
                for (int i = 0; i < strings.length; i++) {
                    if (columns[i].getName().equals(rid)) {
                        strings[i] = columns[i].getName() + " ";
                        switch (db) {
                            case SQLITE:
                                strings[i] += "INTEGER PRIMARY KEY AUTOINCREMENT";
                                break;
                            case JAVADB:
                                strings[i] += "INT NOT NULL GENERATED ALWAYS AS IDENTITY";
                                break;
                            case POSTGRES:
                                strings[i] += columns[i].getType() + " UNIQUE";
                                break;
                        }
                    } else {
                        strings[i] = columns[i].toString();
                    }
                }
                break;
            case PRIMARY: {
                conds = new Condition[columns.length - 1];
                for (int i = 0; i < strings.length; i++) {
                    strings[i] = columns[i].getName() + " IS NOT NULL ";
                    if (i < strings.length - 1) {
                        conds[i] = Condition.AND;
                    }
                }
            }
            break;
            default:
                for (int i = 0; i < strings.length; i++) {
                    strings[i] = columns[i].getName();
                }
        }
        return joinStrings(strings, conds);
    }

    private static String joinValues(final String[] values) {
        String[] stringVals = new String[(values == null) ? 0 : values.length];
        for (int i = 0; i < stringVals.length; i++) {
            if (values[i] != null && !values[i].equals(TIMESTAMP) && !values[i].equals(TRUE) && !values[i].equals(FALSE)) {
                try {
                    Double.parseDouble(values[i]);
                } catch (NumberFormatException e) {
                    if (values[i].contains("'")) {
                        stringVals[i] = ((db == vendor.SQLITE) ? "\"" + values[i] + "\"" : "'" + escapeString(values[i]) + "'");
                    } else {
                        stringVals[i] = "'" + values[i] + "'";
                    }
                    continue;
                }
            }
            stringVals[i] = values[i];
        }
        return joinStrings(stringVals, null);
    }

    static String joinClauses(final boolean update, SelectableFrom[] tables, final String[] colNames, final Type[] colTypes, String[] values, final char[] operators, final Condition[] conditions) {
        values = fixForBool(tables, values);
        if (update) {
            tables = null;
        }
        final String[] whereStrings = new String[values.length];
        for (int i = 0; i < values.length; i++) {
            final String table;
            if (tables == null) {
                table = ""; //intentionally for the case of updating.
            } else {
                table = tables[i].getName() + ".";
            }
            if (values[i] != null && values[i].equals(TIMESTAMP)) {
                whereStrings[i] = ((table != null) ? table : "") + ((colNames != null) ? colNames[i] : "") + ((operators != null) ? operators[i] : "") + values[i];
                continue;
            }
            switch (colTypes[i]) {
                case DATETIME:
                case DATE:
                    whereStrings[i] = (values[i] == null) ? ((update) ? table + colNames[i] + operators[i] + values[i] : table + colNames[i] + " IS NULL") : table + colNames[i] + operators[i] + "DATE('" + escapeString(values[i]) + "')";
                    break;
                case TEXT:
                case CHAR:
                case VARCHAR:
                    whereStrings[i] = table + colNames[i];
                    if (!update && values[i] == null) {
                        whereStrings[i] += " IS NULL";
                        break;
                    } else {
                        whereStrings[i] += operators[i];
                    }
                    if (values[i] == null) {
                        whereStrings[i] += values[i];
                    } else {
                        whereStrings[i] += ((db == vendor.SQLITE) ? "\"" + values[i] + "\"" : "'" + escapeString(values[i]) + "'");
                    }
                    break;
                default:
                    whereStrings[i] = (values[i] == null) ? table + colNames[i] + " IS NULL" : table + colNames[i] + operators[i] + values[i];
            }
        }
        return joinStrings(whereStrings, conditions);
        //TODO: Handle null.
    }

    static String joinWhereClauses(final SelectableFrom[] tables, final String[] colNames, final Type[] colTypes, String[] values, final char[] operators, final Condition[] conditions) {
        return joinClauses(false, tables, colNames, colTypes, values, operators, conditions);
    }

    private static String joinWhereClauses(final SelectableFrom[] tables, final Column[] columns, final String[] values, final char[] operators, final Condition[] conditions) {
        return joinClauses(false, tables, columns, values, operators, conditions);
    }

    private static String joinClauses(final boolean update, final SelectableFrom[] tables, final Column[] columns, final String[] values, final char[] operators, final Condition[] conditions) {
        if (columns.length != values.length) {
            throw new RuntimeException();
        }
        final String[] colNames = new String[columns.length];
        final Type[] colTypes = new Type[columns.length];
        for (int i = 0; i < columns.length; i++) {
            colNames[i] = columns[i].getName();
            colTypes[i] = columns[i].getType();
        }
        return joinClauses(update, tables, colNames, colTypes, values, operators, conditions);
    }

    static String joinWhereClauses(final String[] colNames, final Type[] colTypes, final String[] values, final boolean AND) {
        final char[] operators = new char[values.length];
        final Condition[] conditions = new Condition[values.length];
        for (int i = 0; i < values.length; i++) {
            operators[i] = '=';
            conditions[i] = (AND) ? Condition.AND : Condition.OR;
        }
        return joinWhereClauses(null, colNames, colTypes, values, operators, conditions);
    }

    static String joinWhereClauses(final String[] colNames, final Type[] colTypes, final char[] values, final boolean AND) {
        final String[] sValues = new String[values.length];
        for (int i = 0; i < values.length; i++) {
            sValues[i] = Character.toString(values[i]);
        }
        return joinWhereClauses(colNames, colTypes, sValues, AND);
    }

    static String joinWhereClauses(final String[] colNames, final Type[] colTypes, final int[] vals, final boolean AND) {
        final char[] operators = new char[vals.length];
        final Condition[] conditions = new Condition[vals.length];
        final String[] values = new String[vals.length];
        for (int i = 0; i < vals.length; i++) {
            operators[i] = '=';
            conditions[i] = (AND) ? Condition.AND : Condition.OR;
            values[i] = Integer.toString(vals[i]);
        }
        return joinWhereClauses(null, colNames, colTypes, values, operators, conditions);
    }

    private static String joinUpdateClauses(final Table table, final Column[] columns, final String[] values) {
        final char[] operators = new char[values.length];
        for (int i = 0; i < values.length; i++) {
            operators[i] = '=';
        }
        final Table[] tables = {table};
        return joinClauses(true, tables, columns, values, operators, null);
    }

    private static String onConflict(ConflictResolution res, boolean update) {
        if (res == null || db == vendor.JAVADB) {
            return "";
        }
        return (update) ? "OR " + res : "ON CONFLICT " + res;
    }

    private static String order(final String premise, final Column orderBy, SqlWrapper.Sort order) {
        return premise + " ORDER BY " + orderBy.getName() + " " + order;
    }

    private static String limit(final String premise, final int no) {
        return premise + " LIMIT " + no;
    }

    /**
     * In SQL it corresponds to: "CREATE TABLE " + table.toString().
     * Throws null pointer exception in case table is null.
     * @param table
     * @return
     */
    public static String create(final Table table) {
        if (table == null) {
            throw new NullPointerException();
        }
//        final boolean postgres = SqlWrapper.db.equals(SqlWrapper.vendor.POSTGRES);
        final String ret = "CREATE TABLE " + table;//+ (postgres? " WITH OIDS":"");
        history.add(ret);
        return ret;
    }

    /**
     * In SQL it corresponds to: "INSERT INTO " + table.getName() + "(columns listing comma separated)" VALUES(" + values listing comma separated+")".
     * In case the number of values are fewer than the columns, the not-null default values are included. In this case, input values will be assigned, in
     * index order to the columns that have a null default value. Still in this case, a RuntimeException is thrown in case the number of values is less than the
     * columns without default value.
     * @param view
     * @return
     */
    public static String create(final View view) {
        if (view == null) {
            throw new NullPointerException();
        }
        final String ret = "CREATE VIEW " + view;
        history.add(ret);
        return ret;
    }

    private static String insertSyntax(final ConflictResolution res, final Table table, final String suffix) {
        return "INSERT " + onConflict(res, true) + " INTO " + table.getName() + suffix; // The space before into is necessary in case onConflict(.) returns something
    }

    private static String[] fixForBool(final SelectableFrom[] tables, final boolean[] values){
        final String strings[] = new String[values.length];
        for(int i=0; i<strings.length; i++){
            strings[i] = values[i]? TRUE:FALSE;
        }
        return fixForBool(tables,strings);
    }

    private static String[] fixForBool(final SelectableFrom[] tables, String[] values) {
        if (tables != null) {
            for (SelectableFrom t : tables) {
                if (t.supportBoolean()) {
                    return values;
                }
            }
        }

        for (int i = 0; i < values.length; i++) {
            if (values[i] == null) {
                continue;
            }
            if (values[i].equals(FALSE)) {
                values[i] = "0";
            } else if (values[i].equals(TRUE)) {
                values[i] = "1";
            }
        }

        return values;
    }

    private static String[] fixForBool(final SelectableFrom table, String[] values) {
        final SelectableFrom[] tables = {table};
        return fixForBool(tables, values);
    }

    private static String fixForBool(final SelectableFrom table, final String value) {
        final String[] values = fixForBool(table, new String[]{value});
        return values[0];
    }

    private static String fixForBool(final SelectableFrom table, final boolean value) {
        return fixForBool(table, value ? TRUE : FALSE);
    }

    public static String insert(final ConflictResolution res, final Table table, Column[] columns, String[] values) {

        values = fixForBool(table, values);
        if (columns == null) {
            if (values == null) {
                return insert(table);
            }
            columns = table.getColumns();
            final Column[] tableCols = columns;
            int j = 0;
            if (values.length != columns.length) {
                columns = new Column[values.length];
                for (int i = 0; i < tableCols.length && j < columns.length; i++) {
                    if (tableCols[i].getDef() == null) {
                        columns[j++] = tableCols[i]; //has no default other than perhaps null.
                    }
                }
            }
        }
        final String ret = insertSyntax(res, table, "(" + joinColumns(columns, QueryType.INSERT) + ") VALUES(" + joinValues(values) + ")");
        history.add(ret);
        return ret;
    }

    public static String insert(final Table table, final Column[] columns, final String[] values) {
        return insert(null, table, columns, values);
    }

    public static String insert(final ConflictResolution res, Table table) {
        final String ret = insertSyntax(res, table, " DEFAULT VALUES");
        history.add(ret);
        return ret;
    }

    public static String insert(final Table table) {
        final Column[] cols = table.getColumns();
        final String[] defs = new String[(cols == null) ? 0 : cols.length];
        for (int i = 0; i < defs.length; i++) {
            defs[i] = (String) cols[i].getDef();
        }

        final String ret = insertSyntax(null, table, (db != vendor.JAVADB) ? " DEFAULT VALUES" : "(" + joinColumns(table.getColumns(), QueryType.INSERT) + ") VALUES(" + joinValues(defs) + ")");
        history.add(ret);
        return ret;
    }

    public static String insert(final Table table, final String value) {
        final String[] values = {value};
        return insert(table, null, values);
    }

    public static String insert(final ConflictResolution res, final Table table, final String value) {
        final String[] values = {value};
        return insert(res, table, null, values);
    }

    public static String insert(final Table table, final int value) {
        return insert(table, Integer.toString(value));
    }

    public static String insert(final ConflictResolution res, final Table table, final int value) {
        return insert(res, table, Integer.toString(value));
    }

    /**
     * Converts integer values into Strings and passes them to insert(Table, Column[], String[]).
     * @param res
     * @param table
     * @param columns
     * @param values
     * @return
     */
    public static String insert(final ConflictResolution res, final Table table, final Column[] columns, final double[] values) {
        final String[] stringValues = new String[values.length];
        for (int i = 0; i < values.length; i++) {
            stringValues[i] = Double.toString(values[i]);
        }
        return insert(res, table, columns, stringValues);
    }

    public static String insert(final Table table, final Column[] columns, final double[] values) {
        return insert(null, table, columns, values);
    }

    /**
     * Creates single-element arrays of column and value and passes them to insert(Table, Column[], String[]).
     * @param table
     * @param column
     * @param value
     * @return
     */
    public static String insert(final Table table, final Column column, final String value) {
        final Column[] columns = {column};
        final String[] values = {value};
        return insert(table, columns, values);
    }

    /**
     * Converts Date value into String calling its toString() method and passes it to insert(Table, Column, String).
     * @param table
     * @param column
     * @param value
     * @return
     */
    public static String insert(final Table table, final Column column, final Date value) {
        return insert(table, column, value.toString());
    }

    /**
     * Converts integer value into String and passes it to insert(Table, Column, String).
     * @param table
     * @param column
     * @param value
     * @return
     */
    public static String insert(final Table table, final Column column, final int value) {
        return insert(table, column, Integer.toString(value));
    }

    public static String insert(final Table table, final Column column, final boolean value) {
        return insert(table, column, (value) ? 1 : 0);
    }

    public static String insert(final Table table, final Column[] columns, final Timestamp[] values) {
        final String[] strings = new String[values.length];
        for (int i = 0; i < values.length; i++) {
            strings[i] = values[i].toString();
        }
        return insert(table, columns, strings);
    }

    public static String insert(final Table table, final Column column) {
        return insert(table, column, column.getDef().toString());
    }

    public static String insert(final Table table, final Column[] columns) {
        String[] defs = new String[columns.length];
        for (int i = 0; i < defs.length; i++) {
            defs[i] = columns[i].getDef().toString();
        }
        return insert(table, columns, defs);
    }

    public static String insert(final Table table, final Column[] columns, final String value) {
        String[] values = {value};
        return insert(table, columns, values);
    }

    public static String insert(final Table table, final Column[] columns, final int value) {
        return insert(table, columns, Integer.toString(value));
    }

    public static String insert(final ConflictResolution res, final Table table, final String[] values) {
        return insert(res, table, null, values);
    }

    public static String insertAll(final ConflictResolution res, final Table table, final String[] tuples) {
        if (tuples == null) {
            return null;
        }
        String ret = "";
        for (String t : tuples) {
            ret += insert(res, table, t) + ";\n";
        }
        if (ret.equals("")) {
            return null;
        }
        return ret;
    }

    public static String insert(final ConflictResolution res, final Table table, final List<String> values) {
        return insert(res, table, values.toArray(new String[0]));
    }

    public static String insert(final Table table, final String[] values) {
        return insert(null, table, null, values);
    }

    public static String insert(final ConflictResolution res, final Table table, final int[] vals) {
        return insert(res, table, null, vals);
    }

    public static String insert(final ConflictResolution res, final Table table, final Integer[] vals) {
        String[] stringVals = new String[vals.length];
        for (int i = 0; i < stringVals.length; i++) {
            stringVals[i] = (vals[i] != null) ? vals[i].toString() : null;
        }
        return insert(res, table, null, stringVals);
    }

    public static String insert(final Table table, final double[] values) {
        return insert(table, null, values);
    }

    public static String insert(final Table table, final int[] values) {
        return insert(table, null, values);
    }

    public static String insert(final ConflictResolution res, final Table table, final Column[] columns, final int[] values) {
        final double[] dValues = new double[values.length];
        System.arraycopy(values, 0, dValues, 0, values.length);

        return insert(res, table, columns, dValues);
    }

    public static String insert(final Table table, final Column[] columns, final int[] values) {
        final double[] dValues = new double[values.length];
        for (int i = 0; i < dValues.length; i++) {
            dValues[i] = values[i];
        }
        //System.arraycopy(values, 0, dValues, 0, values.length);
//TODO: fix arrayCopy, or use reference arrays
        return insert(table, columns, dValues);
    }

    private static String where(final String premise, final SelectableFrom[] tables, final Column[] whereColumns, final String[] whereValues, final char[] operators, final Condition[] conditions) {
        SelectableFrom[] tables2 = tables;
        if (tables.length == 1 && whereColumns.length > 1) {
            tables2 = new SelectableFrom[whereColumns.length];
            for (int i = 0; i < whereColumns.length; i++) {
                tables2[i] = tables[0];
            }
        }
        return premise + " WHERE " + joinWhereClauses(tables2, whereColumns, whereValues, operators, conditions);
    }

    private static String where(final String premise, final SelectableFrom[] tables, final Column[] whereColumns, final String[] whereValues, final Condition[] conditions) {
        final char[] operators = new char[whereColumns.length];
        for (int i = 0; i < whereColumns.length; i++) {
            operators[i] = '=';
        }
        return where(premise, tables, whereColumns, whereValues, operators, conditions);
    }

    private static String where(final String premise, final SelectableFrom[] tables, final Column[] whereColumns, final String[] whereValues, final char[] operators) {
        final Condition[] conditions = new Condition[whereColumns.length - 1];
        for (int i = 0; i < whereColumns.length - 1; i++) {
            conditions[i] = Condition.AND;
        }
        return where(premise, tables, whereColumns, whereValues, operators, conditions);
    }

    private static String where(final String premise, final SelectableFrom table, final Column whereColumn, final String whereValue, final char operator) {
        final Condition[] conditions = {Condition.AND};
        final Column[] whereColumns = {whereColumn};
        final String[] whereValues = {whereValue};
        final char[] operators = {operator};
        final SelectableFrom[] tables = {table};
        return where(premise, tables, whereColumns, whereValues, operators, conditions);
    }

    private static String where(final String premise, final SelectableFrom table, final Column whereColumn, final char operator, final String whereValue) {
        return where(premise, table, whereColumn, whereValue, operator);
    }

    private static String where(final String premise, final SelectableFrom table, final Column whereColumn, final char operator, final double whereValue) {
        return where(premise, table, whereColumn, operator, Double.toString(whereValue));
    }

    private static String where(final String premise, final SelectableFrom[] tables, final Column[] whereColumns, final String[] whereValues, final int primaryValueIndex) {
        final Condition[] conditions = new Condition[whereColumns.length];
        final String[] newValues = new String[whereColumns.length];
        newValues[0] = "(" + whereValues[0];
        for (int i = 0; i < whereColumns.length - 1; i++) {
            if (primaryValueIndex == i) {
                conditions[i] = Condition.AND;
                whereValues[i + 1] = ") (" + whereValues[i + 1];
                whereValues[whereColumns.length] = whereValues[whereColumns.length] + ") ";
            } else {
                conditions[i] = Condition.OR;
            }
        }
        return where(premise, tables, whereColumns, whereValues, conditions);
    }

    private static String where(final String premise, final SelectableFrom table, final Column[] whereColumns, final double[] whereValues, final int primaryValueIndex) {
        final String[] stringValues = new String[whereColumns.length];
        final SelectableFrom[] tables = new Table[whereColumns.length];
        for (int i = 0; i < whereColumns.length; i++) {
            stringValues[i] = Double.toString(whereValues[i]);
            tables[i] = table;
        }
        return where(premise, tables, whereColumns, stringValues, primaryValueIndex);
    }

    private static String where(final String premise, final SelectableFrom[] tables, final Column[] whereColumns, final String[] whereValues) {
        final char[] operators = new char[whereColumns.length];
        final Condition[] conditions = new Condition[whereColumns.length - 1];
        for (int i = 0; i < whereColumns.length - 1; i++) {
            operators[i] = '=';
            conditions[i] = Condition.AND;
        }
        operators[operators.length - 1] = '=';
        return where(premise, tables, whereColumns, whereValues, operators, conditions);
    }

    private static String whereNotNull(final String premise, final Column whereColumn) {
        return premise + " WHERE " + whereColumn.getName() + " IS NOT NULL";
    }

    private static String where(final String premise, final SelectableFrom table, final Column[] whereColumns, final String[] whereValues) {
        final SelectableFrom[] tables = new Table[whereColumns.length];
        for (int i = 0; i < whereColumns.length; i++) {
            tables[i] = table;
        }
        return where(premise, tables, whereColumns, whereValues);
    }

    private static String where(final String premise, final SelectableFrom table, final Column whereColumn, final String[] whereValues) {
        final SelectableFrom[] tables = new Table[whereValues.length];
        final Column[] whereColumns = new Column[whereValues.length];
        final Condition[] conditions = new Condition[whereValues.length];

        for (int i = 0; i < whereValues.length; i++) {
            tables[i] = table;
            whereColumns[i] = whereColumn;
            conditions[i] = Condition.OR;
        }
        return where(premise, tables, whereColumns, whereValues, conditions);
    }

    private static String where(final String premise, final SelectableFrom table, final Column whereColumn, final int[] whereVals) {
        final Column[] whereColumns = new Column[whereVals.length];
        for (int i = 0; i < whereVals.length; i++) {
            whereColumns[i] = whereColumn;
        }
        return where(premise, table, whereColumns, whereVals);
    }

    private static String where(final String premise, final SelectableFrom table, final Column[] whereColumns, final int[] whereVals) {
        final String[] whereValues = new String[whereVals.length];
        for (int i = 0; i < whereVals.length; i++) {
            whereValues[i] = Double.toString(whereVals[i]);
        }
        return where(premise, table, whereColumns, whereValues);
    }

    private static String where(final String premise, final SelectableFrom[] whereTables, final Column[] whereColumns,
            final SelectableFrom[] whereTables1, final Column[] whereColumns1) {
        return where(premise, whereTables, whereColumns, joinColumns(whereTables1, whereColumns1));
    }

    private static String where(final String premise, final SelectableFrom table, final Column whereColumn, final String whereValue) {
        final Column[] whereColumns = {whereColumn};
        final String[] whereValues = {whereValue};
        return where(premise, table, whereColumns, whereValues);
    }

    public static String select(final SelectOption selectOptions, final Column[] cols, final SelectableFrom[] tables) {
        return (selectOptions.equals(SelectOption.NONE)) ? "SELECT " + joinColumnsTogether(tables, cols) + " FROM " + joinTables(tables)
                : "SELECT " + selectOptions + "(" + joinColumnsTogether(null, cols, false) + ") FROM " + joinTables(tables);
    }

    private static Column[] getSharedColumns(final SelectableFrom table, final SelectableFrom table1) {

        if (table == null || table.getColumns() == null || table1 == null || table1.getColumns() == null) {
            return null;
        }
        final List<Column> sharedCols = new LinkedList<Column>();
        final Column[] cols = table.getColumns();
        final Column[] cols1 = table1.getColumns();

        for (Column col : cols) {
            for (Column col1 : cols1) {
                if (col.getName().equals(col1.getName())) {
                    sharedCols.add(col1);
                    break;
                }
            }
        }
        return sharedCols.toArray(new Column[0]);
    }

    public static String join(final SelectOption selectOptions, final Column[] what, final SelectableFrom table, final SelectableFrom table1) {
        final Column[] sharedColumns = getSharedColumns(table, table1);
        return join(what, new SelectableFrom[]{table, table1}, new SelectableFrom[]{table}, sharedColumns, new SelectableFrom[]{table1}, sharedColumns);
    }
    

    public static String select(final SelectOption selectOptions, final Column[] what, final SelectableFrom table) {
        final SelectableFrom[] tables = new SelectableFrom[what.length];
        for (int i = 0; i < what.length; i++) {
            tables[i] = table;
        }
        return select(selectOptions, what, tables);
    }

    public static String select(final SelectOption selectOptions, final Column what, final SelectableFrom table) {
        final Column[] whats = {what};
        return select(selectOptions, whats, table);
    }

    public static String select(final SelectOption selectOptions, final Column what, final SelectableFrom table, final Column whereColumn, final char operator, final Date whereDate){
        final String date = new Timestamp(whereDate.getTime()).toString();
        return select(selectOptions, what, table, whereColumn, operator, date);
    }

    public static String select(final Column what, final SelectableFrom table, final Column whereColumn, final char operator, final String whereValue){
        return select(SelectOption.NONE,what, table, whereColumn, operator, whereValue);
    }

    public static String select(final Column what, final SelectableFrom table, final Column whereColumn, final char operator, final Date whereValue){
        final String date = new Timestamp(whereValue.getTime()).toString();
        return select(what, table, whereColumn, operator, date);
    }

    public static String select(final SelectOption selectOption, final Column what, final SelectableFrom table, final Column whereColumn, final char operator, final String whereValue){
        final String ret =  where(select(selectOption, what, table), table, whereColumn, operator, whereValue);
        history.add(ret);
        return ret;
    }

    /**
     * In SQL it corresponds to: "SELECT " listing of columns comma separated + " FROM "+table.getName()".
     * @param cols
     * @param table
     * @return
     */
    public static String select(final Column[] cols, final SelectableFrom table) {
        final String ret = select(SelectOption.NONE, cols, table);
        history.add(ret);
        return ret;
    }

    /**
     * Creates single element columns array and passes it to select(Table, Column[]).
     * @param table
     * @param what
     * @return
     */
    public static String select(final Column what, final SelectableFrom table) {
        final Column[] whats = {what};
        return select(whats, table);
    }

    public static String select(final SelectOption selectOptions, final Column[] what, final SelectableFrom[] tables, final SelectableFrom[] whereTables, final Column[] whereColumns,
            final String[] whereValues) {
        return where(select(selectOptions, what, tables), whereTables, whereColumns, whereValues);
    }

    public static String select(final SelectOption selectOptions, final Column[] what, final SelectableFrom[] tables, final SelectableFrom[] whereTables, final Column[] whereColumns,
            final char[] operators, final String[] whereValues) {
        return where(select(selectOptions, what, tables), whereTables, whereColumns, whereValues, operators);
    }

    public static String select(final SelectOption selectOptions, final Column[] what, final SelectableFrom table, final Column[] whereColumns,
            final String[] whereValues) {
        final SelectableFrom[] tables = new SelectableFrom[what.length];
        for (int i = 0; i < what.length; i++) {
            tables[i] = table;
        }
        final String ret = select(selectOptions, what, tables, tables, whereColumns, whereValues);
        history.add(ret);
        return ret;
    }

    public static String select(final SelectOption selectOptions, final Column[] whats, final SelectableFrom table, final Column whereColumn,
            final String whereValue) {
        final Column[] whereColumns = {whereColumn};
        final String[] whereValues = {whereValue};
        return select(selectOptions, whats, table, whereColumns, whereValues);
    }

    public static String select(final SelectOption selectOptions, final Column what, final SelectableFrom table, final Column whereColumn,
            final String whereValue) {
        final Column[] whats = {what};
        return select(selectOptions, whats, table, whereColumn, whereValue);
    }

    public static String select(final SelectOption selectOptions, final Column[] whats, final SelectableFrom[] tables,
            final SelectableFrom[] whereTables, final Column[] whereColumns, final SelectableFrom[] whereTables1,
            final Column[] whereColumns1) {
        return where(select(selectOptions, whats, tables), whereTables, whereColumns, whereTables1, whereColumns1);
    }

    public static String select(Column what, SelectableFrom table, SelectableFrom table1, Column[] whereColumn, Column[] whereColumn1) {
        final SelectableFrom[] tables= {table, table1};

        return select(SelectOption.NONE, new Column[]{what},tables, new SelectableFrom[]{tables[0]},
                whereColumn, new SelectableFrom[]{tables[1]}, whereColumn1);
    }

    public static String selectIds(final Table[] tables, final SelectableFrom[] whereTables, final Column[] whereColumns, final char[] operators, final String[] whereValues) {
        final Column[] idCols = new Column[tables.length];
        for (int i = 0; i < tables.length; i++) {
            idCols[i] = tables[i].getIdColumn();
        }
        return select(idCols, tables, whereTables, whereColumns, whereValues);
    }

    public static String selectId(final Table table, final Column[] whereColumns, final char[] operators, final String[] whereValues) {
        return select(table.getIdColumn(), table, whereColumns, operators, whereValues);
    }

    public static String selectId(final Table table, final Column whereColumn, final String whereValue) {
        return select(table.getIdColumn(), table, whereColumn, whereValue);
    }

    public static String selectId(final Table table, final Column whereColumn, final Date whereDate) {
        return select(table.getIdColumn(), table, whereColumn, whereDate);
    }

    public static String selectId(final Table table, final Column[] whereCols, final String[] whereValues) {
        return select(table.getIdColumn(), table, whereCols, whereValues);
    }

    public static String selectId(final Table table, final Column[] whereCols, final Integer[] whereValues) {
        return select(table.getIdColumn(), table, whereCols, whereValues);
    }

    public static String selectId(final Table table, final Column[] whereCols, final int[] whereVals) {
        return select(table.getIdColumn(), table, whereCols, whereVals);
    }

    public static String selectId(final Table table) {
        return select(table.getIdColumn(), table);
    }

    public static String selectId(final Table table, final String[] values) {
        return select(table.getIdColumn(), table, values);
    }

    public static String selectId(final Table table, final int[] vals) {
        return select(table.getIdColumn(), table, vals);
    }

    public static String selectId(final Table table, final String value) {
        return selectId(table, new String[]{value});
    }

    public static String selectId(final Table table, final Column whereColumn, final int whereVal) {
        return selectId(table, whereColumn, Integer.toString(whereVal));
    }

    public static String selectId(final Table table, final Column[] whereColumns, final char[] operators, final int[] vals) {
        final String[] stringVals = new String[vals.length];
        for (int i = 0; i < stringVals.length; i++) {
            stringVals[i] = Integer.toString(vals[i]);
        }
        return selectId(table, whereColumns, operators, stringVals);
    }

    public static String selectId(final Table table, final Column whereColumn, final boolean whereValue, Sort order) {
        return select(table.getIdColumn(), table, whereColumn, whereValue, order);
    }

    public static String selectId(final Table table, final Column whereColumn, final Date whereValue, Sort order) {
        return select(table.getIdColumn(), table, whereColumn, whereValue, order);
    }

    public static String selectId(final Table table, final Column orderBy, Sort order) {
        return select(table.getIdColumn(), table, orderBy, order);
    }

    public static String selectIdWhereNotNull(final Table table, final Column orderBy, Sort order, final Column notNullColumn) {
        return whereNotNull(select(table.getIdColumn(), table, orderBy, order), notNullColumn);
    }

    public static String selectIdWhereNotNull(final Table table, final Column notNullColumn) {
        return whereNotNull(selectId(table), notNullColumn);
    }

    public static String countWhereNotNull(final Table table, final Column notNullColumn) {
        return whereNotNull(count(table), notNullColumn);
    }

    public static String select(final Column[] what, final SelectableFrom[] tables, final SelectableFrom[] whereTables, final Column[] whereColumns,
            final char[] operators, final String[] whereValues) {
        final String ret = select(SelectOption.NONE, what, tables, whereTables, whereColumns, operators, whereValues);
        history.add(ret);
        return ret;
    }

    public static String select(final Column what, final SelectableFrom table, final String[] whereValues) {
        return select(what, table, table.getColumns(), whereValues);
    }

    public static String selectDistinct(final Column what, final SelectableFrom table) {
        return select(SelectOption.DISTINCT, what, table);
    }

    public static String select(final Column what, final SelectableFrom table, final int[] whereVals) {
        return select(what, table, table.getColumns(), whereVals);
    }

    public static String select(final Column what, final SelectableFrom table, final Column[] whereColumns, final char[] operators,
            final String[] whereValues) {
        final Column[] whats = {what};
        return select(whats, table, whereColumns, operators, whereValues);
    }

    public static String select(final Column[] what, final SelectableFrom table, final Column[] whereColumns, final char[] operators, final String[] whereValues) {
        final SelectableFrom[] tables = {table};
        return select(what, tables, tables, whereColumns, operators, whereValues);
    }

    public static String select(final Column[] whats, final SelectableFrom table, final char[] operators, final String[] whereValues) {
        return select(whats, table, whats, operators, whereValues);
    }

    public static String select(final Column[] whats, final SelectableFrom table, final char[] operators, final double[] whereVals) {
        final String[] strings = new String[whereVals.length];
        for (int i = 0; i < whereVals.length; i++) {
            strings[i] = Double.toString(whereVals[i]);
        }
        return select(whats, table, whats, operators, strings);
    }

    public static String select(final Column[] whats, final SelectableFrom table, final Column[] whereColumns, final char[] operators, final double[] whereVals) {
        final String[] strings = new String[whereVals.length];
        for (int i = 0; i < whereVals.length; i++) {
            strings[i] = Double.toString(whereVals[i]);
        }
        return select(whats, table, whats, operators, strings);
    }

    public static String select(final Column[] whats, final SelectableFrom table, final Column[] whereColumns, final char[] operators, final int[] whereVals) {
        final String[] strings = new String[whereVals.length];
        for (int i = 0; i < whereVals.length; i++) {
            strings[i] = Double.toString(whereVals[i]);
        }
        return select(whats, table, whereColumns, operators, strings);
    }

    public static String select(final Column what, final SelectableFrom table, final Column[] whereColumns, final char[] operators, final int[] whereVals) {
        final Column[] whats = {what};
        return select(whats, table, whereColumns, operators, whereVals);
    }

    public static String select(final Column[] whats, final SelectableFrom table, final char[] operators, final int[] whereVals) {
        final String[] strings = new String[whereVals.length];
        for (int i = 0; i < whereVals.length; i++) {
            strings[i] = Double.toString(whereVals[i]);
        }
        return select(whats, table, whats, operators, strings);
    }

    public static String select(final Column[] whats, final SelectableFrom table, final char[] operators, final java.util.Date[] whereValues) {
        final String[] strings = new String[whereValues.length];
        for (int i = 0; i < whereValues.length; i++) {
            strings[i] = new Timestamp(whereValues[i].getTime()).toString();
        }
        return select(whats, table, whats, operators, strings);
    }

    public static String select(final Column[] whats, final SelectableFrom table, final char[] operators, final Timestamp[] whereValues) {
        final String[] strings = new String[whereValues.length];
        for (int i = 0; i < whereValues.length; i++) {
            strings[i] = whereValues[i].toString();
        }
        return select(whats, table, whats, operators, strings);
    }

    public static String select(final Column[] whats, final SelectableFrom[] tables,
            final SelectableFrom[] whereTables, final Column[] whereColumns, final SelectableFrom[] whereTables1,
            final Column[] whereColumns1) {
        return select(SelectOption.NONE, whats, tables, whereTables, whereColumns, whereTables1, whereColumns1);
    }

    public static String select(final Column what, final SelectableFrom table, final Column whereColumn, final String whereValue, SqlWrapper.Sort order) {
        return order(select(what, table, whereColumn, whereValue), what, order);
    }

    public static String select(final Column what, final SelectableFrom table, final Column whereColumn, final Date whereValue, SqlWrapper.Sort order) {
        return order(select(what, table, whereColumn, whereValue), what, order);
    }

    public static String select(final Column what, final SelectableFrom table, final Column whereColumn, final int whereValue, SqlWrapper.Sort order) {
        return select(what, table, whereColumn, Integer.toString(whereValue), order);
    }

    public static String select(final Column what, final SelectableFrom table, final Column whereColumn, final boolean whereValue, SqlWrapper.Sort order) {
        return select(what, table, whereColumn, whereValue ? 1 : 0, order);
    }

    public static String select(final Column what, final SelectableFrom table, final Column whereColumn, final boolean whereValue) {
        return select(what, table, whereColumn, whereValue ? 1 : 0);
    }

    public static String select(final Column[] what, final SelectableFrom table, Sort order) {
        return select(what, table, what[0], order);
    }

    public static String select(final Column[] what, final SelectableFrom table, final Column orderBy, Sort order) {
        return order(select(what, table), orderBy, order);
    }

    public static String select(final Column what, final SelectableFrom table, final Column orderBy, Sort order) {
        final Column[] whats = {what};
        return select(whats, table, orderBy, order);
    }

    public static String select(final Column[] whats, final SelectableFrom table, final Column whereColumns[], final String whereWhats[], Sort order) {
        return order(select(SelectOption.NONE, whats, table, whereColumns, whereWhats), whats[0], order);
    }

    public static String select(final Column[] what, final SelectableFrom[] tables, final SelectableFrom[] whereTables, final Column[] whereColumn, final String[] whereValues) {
        return select(SelectOption.NONE, what, tables, whereTables, whereColumn, whereValues);
    }

    public static String selectWhereId(final Column[] what, final Table table, final int id) {
        return select(what, table, table.getIdColumn(), id);
    }

    public static String selectWhereId(final Column what, final Table table, final int id) {
        return selectWhereId(new Column[]{what}, table, id);
    }

    public static String select(final Column[] what, final SelectableFrom[] tables, final SelectableFrom whereTable, final Column[] whereColumn, final String[] whereValues) {
        final SelectableFrom[] whereTables = new SelectableFrom[whereColumn.length];
        for (int i = 0; i < whereColumn.length; i++) {
            whereTables[i] = whereTable;
        }
        return select(what, tables, whereTables, whereColumn, whereValues);
    }

    public static String join(final Column[] whats, final SelectableFrom[] tables, final SelectableFrom[] whereTables, final Column[] whereColumns, final SelectableFrom[] whereTables1, final Column[] whereColumns1) {
        final String ret = where(select(SelectOption.NONE, whats, tables), whereTables, whereColumns, whereTables1, whereColumns1);
        history.add(ret);
        return ret;
    }

    public static String join(final Column what, final SelectableFrom[] tables, final SelectableFrom[] whereTables, final Column[] whereColumns, final SelectableFrom[] whereTables1, final Column[] whereColumns1){
        return join(new Column[]{what}, tables, whereTables, whereColumns, whereTables1, whereColumns1);
    }
    

    /**
     * Creates a column named "*" and passes it to select(Table,Column).
     * @param table
     * @return
     */
    public static String selectAll(final SelectableFrom table) {
        return select(all, table);
    }

    public static String selectAll(final SelectableFrom table, final Column whereColumn, final boolean whereValue) {
        return selectAll(table, whereColumn, fixForBool(table, whereValue));
    }

    /**
     * In SQL it corresponds to: "SELECT " listing of whats comma-separated prefixed by the corresponding tables in index order " FROM " + listing of tables + " WHERE " + listing of
     * comma separated whereColumns prefixed by the corresponding tables in index order, equated with the corresponding whereValues, still in index order.
     * @param tables
     * @param whats
     * @param whereColumns
     * @param whereValues
     * @return
     */
    public static String select(final Column[] whats, final SelectableFrom[] tables, final Column[] whereColumns, final String[] whereValues) {
        return select(SelectOption.NONE, whats, tables, tables, whereColumns, whereValues);
    }

    public static String select(final Column what, final SelectableFrom table, final Column[] whereColumns, final String[] whereValues) {
        final Column[] whats = {what};
        return select(SelectOption.NONE, whats, table, whereColumns, whereValues);
    }

    public static String select(final Column what, final SelectableFrom table, final Column[] whereColumns, final int[] whereValues) {
        String[] strings = new String[whereValues.length];
        for (int i = 0; i < whereValues.length; i++) {
            strings[i] = Integer.toString(whereValues[i]);
        }
        return select(what, table, whereColumns, strings);
    }

    public static String select(final Column what, final SelectableFrom table, final Column[] whereColumns, final Integer[] whereValues) {
        String[] strings = new String[whereValues.length];
        for (int i = 0; i < whereValues.length; i++) {
            if (whereValues[i] != null) {
                strings[i] = whereValues[i].toString();
            }
        }
        return select(what, table, whereColumns, strings);
    }

    /**
     * Creates single element arrays of table, cols, whereColumn and whereValue and passes them to select(Table[], Column[], Column[], String[]).
     * @param what
     * @param table
     * @param whereColumn
     * @param whereValue
     * @return
     */
    public static String select(final Column what, final SelectableFrom table, final Column whereColumn, final String whereValue) {
        return select(SelectOption.NONE, what, table, whereColumn, whereValue);
    }

    public static String select(final Column what, final SelectableFrom table, final Column whereColumn, final Timestamp whereValue) {
        return select(what, table, whereColumn, (whereValue == null) ? null : whereValue.toString());
    }

    public static String max(final Column what, final SelectableFrom table) {
        return select(SelectOption.MAX, what, table);
    }

    public static String min(final Column what, final SelectableFrom table) {
        return select(SelectOption.MIN, what, table);
    }

    public static String selectMinId(final Table table, final Column whereColumn, final String whereValue) {
        final String ret = select(SelectOption.MIN, table.getIdColumn(), table, whereColumn, whereValue);
        history.add(ret);
        return ret;
    }

    public static String min(final Table table) {
        return min(table.getIdColumn(), table);
    }

    public static String selectMaxId(final Table table, final Column whereColumn, final String whereValue) {
        final String ret = select(SelectOption.MAX, table.getIdColumn(), table, whereColumn, whereValue);
        history.add(ret);
        return ret;
    }

    public static String selectMaxId(final Table table, final Column whereColumn, final int whereVal) {
        return selectMaxId(table, whereColumn, Integer.toString(whereVal));
    }

    public static String selectMaxId(final Table table, final Column whereColumn, final Date whereDate) {
        return selectMaxId(table, whereColumn, (whereDate == null) ? null : whereDate.toString());
    }

    public static String max(final Table table) {
        return max(table.getIdColumn(), table);
    }

    public static String select(final Column what, final SelectableFrom table, final Column whereColumn, final double whereValue) {
        return select(what, table, whereColumn, Double.toString(whereValue));
    }

    public static String select(final Column what, final SelectableFrom table, final Column whereColumn, final int whereValue) {
        return select(what, table, whereColumn, (double) whereValue);
    }

    public static String select(final Column[] what, final SelectableFrom table, final Column whereColumn, final char operator, final int whereValue) {
        return where(select(what, table), table, whereColumn, operator, (double) whereValue);
    }

    public static String select(final Column what, final SelectableFrom table, final Column whereColumn, final char operator, final int whereValue) {
        return where(select(what, table), table, whereColumn, operator, (double) whereValue);
    }

    public static String select(final Column[] whats, final SelectableFrom table, final Column whereColumn, final String whereValue) {
        return select(SelectOption.NONE, whats, table, whereColumn, whereValue);
    }

    public static String select(final Column[] whats, final SelectableFrom table, final Column[] whereColumn, final String[] whereValue) {
        return select(SelectOption.NONE, whats, table, whereColumn, whereValue);
    }

    public static String select(final Column[] whats, final SelectableFrom table, final Column[] whereColumn, final double[] whereValue) {
        final String[] strings = new String[whereValue.length];
        for (int i = 0; i < whereValue.length; i++) {
            strings[i] = Double.toString(whereValue[i]);
        }
        return select(whats, table, whereColumn, strings);
    }

    public static String select(final Column[] whats, final SelectableFrom table, final Column[] whereColumn, final int[] whereValue) {
        final String[] strings = new String[whereValue.length];
        for (int i = 0; i < whereValue.length; i++) {
            strings[i] = Double.toString(whereValue[i]);
        }
        return select(whats, table, whereColumn, strings);
    }

    public static String select(final Column[] whats, final SelectableFrom table, final Column whereColumn, final double whereValue) {
        return select(whats, table, whereColumn, Double.toString(whereValue));
    }

    public static String select(final Column what, final SelectableFrom table, final Column whereColumn, final Date whereValue) {
        return select(what, table, whereColumn, (whereValue == null) ? null : new SimpleDateFormat(FULL_DATE_PATTERN).format(whereValue));
    }



    public static String select(final Column[] whats, final SelectableFrom table, final Column whereColumn, final int whereValue) {
        return select(whats, table, whereColumn, (double) whereValue);
    }
    /*
    private static String select(final String selectOption, final SelectableFrom table, final Column[] groupBy){
    return groupBy(selectAll(selectOption, table),groupBy);
    }
     */

    private static String groupBy(final String prefix, final SelectableFrom[] tables, final Column[] groupBy) {

        return prefix + " GROUP BY " + joinStrings(joinColumns(tables, groupBy), null);
    }

    private static String groupBy(final String prefix, final SelectableFrom table, final Column groupBy) {
        return groupBy(prefix, new SelectableFrom[]{table}, new Column[]{groupBy});
    }

    private static String having(final String prefix, final String selectOption, final char havingOperator, final int havingVal) {
        return prefix + " HAVING " + selectOption + havingOperator + havingVal;
    }

    /**
     *  Creates column with name "*" and passes it to select(Table,Column,Column,String).
     * @param table
     * @param whereColumn
     * @param whereValue
     * @return
     */
    public static String selectAll(final SelectableFrom table, final Column whereColumn, final String whereValue) {
        return select(all, table, whereColumn, whereValue);
    }

    private static String selectAll(final SelectOption selectOption, final SelectableFrom table) {
        return select(selectOption, all, table);
    }

    public static String selectAll(final SelectableFrom table, final Column[] whereColumn, final String[] whereValues) {
        return select(all, table, whereColumn, whereValues);
    }

    public static String selectAll(final SelectableFrom table, final Column whereColumn, final int whereValue) {
        return select(all, table, whereColumn, whereValue);
    }

    public static String last(final Column what, final SelectableFrom table, int no) {
        return limit(order(select(what, table), what, Sort.DESC), no);
    }

    public static String first(final Column what, final SelectableFrom table, int no) {
        return limit(order(select(what, table), what, Sort.ASC), no);
    }

    public static String first(final Column what, final SelectableFrom table) {
        return first(what, table, 1);
    }

    public static String last(final Column what, final SelectableFrom table) {
        final String idColumn = table.getIdColumn().getName();
        final String ret = "SELECT " + what.getName() + " FROM " + table.getName() + " WHERE " + idColumn + " >= (SELECT MAX(" + idColumn + ") FROM " + table.getName() + ")";
        if (db != vendor.JAVADB) {
            return limit(order(select(what, table), what, Sort.DESC), 1);
        } else {
            history.add(ret);
        }
        return ret;
    }

    public static String last(final Table table) {
        return last(table.getIdColumn(), table);
    }

    public static String last(final Column what, final SelectableFrom table, final Column whereColumn, final String whereValue, int no) {
        return limit(order(select(what, table, whereColumn, whereValue), what, Sort.DESC), no);
    }

    public static String last(final Column what, final SelectableFrom table, final Column whereColumn, final char operator, final int whereValue) {
        return limit(order(select(what, table, whereColumn, operator, whereValue), what, Sort.DESC), 1);
    }

    public static String last(final Column what, final SelectableFrom table, final Column whereColumn, final String whereValue) {
        final String ret = "SELECT " + what.getName() + " FROM " + table.getName() + " WHERE " + whereColumn.getName() + "='" + whereValue + "' AND " + table.getIdColumn().getName() + " >= (SELECT MAX(" + table.getIdColumn().getName() + ") FROM " + table.getName() + ")";
        if (db != vendor.JAVADB) {
            return limit(order(select(what, table, whereColumn, whereValue), what, Sort.DESC), 1);
        } else {
            history.add(ret);
        }
        return ret;
    }

    public static String last(final Column what, final SelectableFrom table, final Column whereColumn, final double whereValue) {
        return limit(order(select(what, table, whereColumn, whereValue), what, Sort.DESC), 1);
    }

    public static String last(final Column what, final SelectableFrom table, final Column whereColumn, final Timestamp whereValue) {
        return limit(order(select(what, table, whereColumn, whereValue), what, Sort.DESC), 1);
    }

    public static String first(final Column what, final SelectableFrom table, final Column whereColumn, final String whereValue, int no) {
        return limit(order(select(what, table, whereColumn, whereValue), what, Sort.ASC), no);
    }

    public static String first(final Column what, final SelectableFrom table, final Column whereColumn, final String whereValue) {
        return first(what, table, whereColumn, whereValue, 1);
    }

    public static String first(final Column what, final SelectableFrom table, final Column whereColumn, final int whereValue) {
        return limit(order(select(what, table, whereColumn, whereValue), what, Sort.ASC), 1);
    }

    public static String first(final Column what, final SelectableFrom table, final Column whereColumn, final double whereValue) {
        return limit(order(select(what, table, whereColumn, whereValue), what, Sort.ASC), 1);
    }

    public static String last(final Column what, final SelectableFrom table, final Column whereColumn, final int whereValue, final int no) {
        return last(what, table, whereColumn, Integer.toString(whereValue), no);
    }

    public static String last(final Column what, final SelectableFrom table, final Column whereColumn, final int whereValue) {
        return last(what, table, whereColumn, whereValue, 1);
    }

    public static String count(final Column[] what, final SelectableFrom table, final Column whereColumn[], final String[] whereWhat) {
        return select(SelectOption.COUNT, what, table, whereColumn, whereWhat);
    }

    public static String count(final SelectableFrom table) {
        return count(all, table);
    }

    public static String count(final Column what, final SelectableFrom table) {
        return select(SelectOption.COUNT, what, table);
    }

    public static String count(final Column what, final SelectableFrom table, final Column whereColumn, final String whereWhat) {
        final Column[] whats = {what};
        final Column[] whereColumns = {whereColumn};
        final String[] whereWhats = {whereWhat};
        return count(whats, table, whereColumns, whereWhats);
    }

    public static String count(final SelectableFrom table, final Column groupBy, final char havingOperator, final int havingVal) {
        final String ret = having(groupBy(selectAll(SelectOption.COUNT, table), table, groupBy), "COUNT(*)", havingOperator, havingVal);
        history.add(ret);
        return ret;
    }

    public static String count(final Column what, final SelectableFrom table, final Column whereColumn, final double whereWhat) {
        return count(what, table, whereColumn, Double.toString(whereWhat));
    }

    public static String count(final Column what, final SelectableFrom table, final Column whereColumn, final int whereWhat) {
        return count(what, table, whereColumn, (double) whereWhat);
    }

    public static String count(final SelectableFrom table, final Column whereColumn, final int whereWhat) {
        return count(all, table, whereColumn, (double) whereWhat);
    }

    public static String update(final Table table, final Column column, final double value, final Column whereColumn, final double whereWhat) {
        return update(table, column, value, whereColumn, Double.toString(whereWhat));
    }

    public static String update(final Table table, final Column column, final double value, final Column whereColumn, final int whereWhat) {
        return update(table, column, value, whereColumn, Double.toString(whereWhat));
    }

    public static String update(final Table table, final Column column, final String value, final Column whereColumn, final double whereVal) {
        return update(table, column, value, whereColumn, Double.toString(whereVal));
    }

    public static String update(final Table table, final Column[] columns, final String[] values, final Column[] whereColumns, final String[] whereValues) {
        final String ret = where(set(table, columns, values), table, whereColumns, whereValues);
        history.add(ret);
        return ret;
    }

    public static String update(final Table table, final Column column, final String value) {
        return update(table, new Column[]{column}, new String[]{value});
    }

    public static String update(final Table table, final Column column, final int value) {
        return update(table, column, Integer.toString(value));
    }

    public static String update(final Table table, final Column column, final boolean value, final Column whereColumn, final int whereWhat) {
        return update(table, column, fixForBool(table, (value ? TRUE : FALSE)), whereColumn, (double) whereWhat);
    }

    public static String update(final Table table, final Column column, final boolean value, final Column whereColumn, final String[] whereValues) {
        final Column[] whereColumns = new Column[whereValues.length];
        for (int i = 0; i < whereValues.length; i++) {
            whereColumns[i] = whereColumn;
        }
        return update(table, column, value, whereColumns, whereValues);
    }

    public static String update(final Table table, final Column column, final boolean value, final Column whereColumn, final int[] whereVals) {
        final String[] whereValues = new String[whereVals.length];
        for (int i = 0; i < whereVals.length; i++) {
            whereValues[i] = Integer.toString(whereVals[i]);
        }
        return update(table, column, value, whereColumn, whereValues);
    }

    public static String update(final Table table, final Column column, final boolean value, final Column[] whereColumns, final String[] whereValues) {
        return update(table, column, fixForBool(table, (value ? TRUE : FALSE)), whereColumns, whereValues);
        //return where(set(table, column, fixForBool(table, (value? TRUE: FALSE)), table, whereColumns, whereValues);
    }

    public static String update(final Table table, final Column[] columns, final boolean[] values, final Column[] whereColumns, final String[] whereValues){
        return update(table, columns, fixForBool(new Table[]{table}, values), whereColumns, whereValues);
    }

    public static String update(final Table table, final Column column, final boolean value, final Column[] whereColumns, final int[] whereValues) {
        return update(table, column, fixForBool(table, (value ? TRUE : FALSE)), whereColumns, whereValues);
    }

    public static String update(final Table table, final Column column, final Date value, final Column whereColumn, final double whereWhat) {
        return update(table, column, value.toString(), whereColumn, Double.toString(whereWhat));
    }

    public static String update(final Table table, final Column column, final String value, final Column whereColumn, final String whereWhat) {
        return update(table, new Column[]{column}, new String[]{value}, new Column[]{whereColumn}, new String[]{whereWhat});
    }

    public static String update(final Table table, final Column column, final String newValue, final String oldValue) {
        return update(table, column, newValue, column, oldValue);
    }

    public static String update(final Table table, final Column column, final String value, final Column[] whereColumns, final String[] whereValues) {
        return update(table, new Column[]{column}, new String[]{value}, whereColumns, whereValues);
    }

    public static String update(final Table table, final Column[] columns, final String[] values, final Column[] whereColumns, final int[] whereVals) {
        final String[] whereValues = new String[whereVals.length];
        for (int i = 0; i < whereValues.length; i++) {
            whereValues[i] = Integer.toString(whereVals[i]);
        }
        return update(table, columns, values, whereColumns, whereValues);
    }

    public static String update(final Table table, final Column column, final String value, final Column[] whereColumns, final int[] whereVals) {
        return update(table, new Column[]{column}, new String[]{value}, whereColumns, whereVals);
    }
    public static long millisTestOffset = 0;

    public static String updateTime(final Table table, final Column column, final Column whereColumn, final double whereWhat) {
        return update(table, column, getDate(), whereColumn, whereWhat);
    }

    public static String getDate() {
        if (db != vendor.JAVADB) {
            return "(SELECT DATE('now'," + millisTestOffset / 1000 + "))";
        }
        return new Date(System.currentTimeMillis() + millisTestOffset).toString();//"CURRENT_DATE ";
    }

    public static String toString(final Date date){
        return new Timestamp(date.getTime()).toString();
    }

//    public static String updateTime(final Table table, final Column column, final Column whereColumn, final int[] whereWhat) {
//        return update(table, column, getDate(), whereColumn, whereWhat);
//    }
//
//    public static String updateTime(final Table table, final Column column, final Column whereColumn, final Integer[] whereWhat) {
//        return update(table, column, getDate(), whereColumn, whereWhat);
//    }
//
    public static String update(final Table table, final Column[] columns, final String[] values) {
        return set(table, columns, values);
    }

    public static String update(final Table table, final Column[] columns, final String[] values, final Column whereColumn, final String whereValue) {
        return where(set(table, columns, values), table, whereColumn, whereValue);
    }

    public static String update(final Table table, final Column[] columns, final int[] vals, final Column whereColumn, final String whereValue) {
        final String[] values = new String[vals.length];
        for (int i = 0; i < vals.length; i++) {
            values[i] = Integer.toString(vals[i]);
        }
        return where(set(table, columns, values), table, whereColumn, whereValue);
    }

    public static String update(final Table table, final Column[] columns, final int[] vals, final Column whereColumn, final int whereValue) {
        return update(table, columns, vals, whereColumn, Integer.toString(whereValue));
    }

    public static String update(final Table table, final Column[] columns, final double[] values, final Column whereColumn, final String whereValue) {
        return where(set(table, columns, values), table, whereColumn, whereValue);
    }

    public static String update(final Table table, final Column[] columns, final double[] values, final Column whereColumn, final int whereValue) {
        return update(table, columns, values, whereColumn, Double.toString(whereValue));
    }

    public static String update(final Table table, final Column[] columns, final double[] values, final Column whereColumn, final double whereValue) {
        return update(table, columns, values, whereColumn, Double.toString(whereValue));
    }

    public static String update(final Table table, final Column column, final String value, final Column whereColumn,
            final int whereWhat) {
        return update(table, column, value, whereColumn, (double) whereWhat);
    }

    public static String update(final Table table, final Column column, final boolean value, final Column whereColumn,
            final String whereWhat) {

        return update(table, column, fixForBool(table, value ? TRUE : FALSE), whereColumn, whereWhat);
    }

    public static String update(final Table table, final Column column, final int value, final Column whereColumn,
            final String whereWhat) {
        return update(table, column, (double) value, whereColumn, whereWhat);
    }

    public static String update(final Table table, final Column column, final String value, final Column whereColumn,
            final Timestamp whereWhat) {
        return update(table, column, value, whereColumn, new SimpleDateFormat(FULL_DATE_PATTERN).format(whereWhat));
    }

    public static String update(final Table table, final Column column, final int value, final Column whereColumn,
            final Timestamp whereWhat) {
        return update(table, column, Integer.toString(value), whereColumn, whereWhat);
    }

    public static String update(final Table table, final Column column, final Timestamp value, final Column whereColumn,
            final int whereWhat) {
        return update(table, column, new SimpleDateFormat(FULL_DATE_PATTERN).format(value), whereColumn, whereWhat);
    }

    public static String update(final Table table, final Column column, final Timestamp value, final Column whereColumn,
            final Timestamp whereWhat) {
        return update(table, column, new SimpleDateFormat(FULL_DATE_PATTERN).format(value), whereColumn, whereWhat);
    }

    public static String update(final Table table, final Column column, final double value, final Column whereColumn,
            final String whereWhat) {
        return update(table, column, Double.toString(value), whereColumn, whereWhat);
    }

    public static String update(final Table table, final String[] values, final int primaryValueIndex) {
        final Table[] tables = new Table[values.length];
        for (int i = 0; i < values.length; i++) {
            tables[i] = table;
        }
        return where(set(table, table.getColumns(), values), tables, table.getColumns(), values, primaryValueIndex);
    }

    public static String update(final Table table, final double[] values, final int primaryValueIndex) {
        return where(set(table, table.getColumns(), values), table, table.getColumns(), values, primaryValueIndex);
    }

    public static String setNull(final Table table, final Column column, final Column whereColumn, final double whereWhat) {
        return update(table, column, null, whereColumn, Double.toString(whereWhat));
    }

    public static String setNull(final Table table, final Column column, final Column whereColumn, final long whereWhat) {
        return update(table, column, null, whereColumn, Double.toString(whereWhat));
    }

    public static String setNull(final Table table, final Column column, final Column whereColumn, final Date whereWhat) {
        return update(table, column, null, whereColumn, whereWhat.toString());
    }

    public static String setNull(final Table table, final Column column) {
        return update(table, column, null);
    }

    public static String setNull(final Table table, final Column column, final Column whereColumn, final String whereWhat) {
        return update(table, column, null, whereColumn, whereWhat);
    }

    public static String setNullWhereId(final Table table, final Column column, final int whereId) {
        return setNull(table, column, table.getIdColumn(), whereId);
    }

    public static String increment(final Table table, final Column what, final double increment, final Column whereColumn, final String whereWhat) {
        return update(table, what, what.getName() + "+" + Double.toString(increment), whereColumn, whereWhat);
    }

    public static String increment(final Table table, final Column what, final int increment, final Column whereColumn, final String whereWhat) {
        return increment(table, what, (double) increment, whereColumn, whereWhat);
    }

    public static String increment(final Table table, final Column what, final double increment, final Column whereColumn, final double whereWhat) {
        return increment(table, what, increment, whereColumn, Double.toString(whereWhat));
    }

    public static String increment(final Table table, final Column what, final int increment, final Column whereColumn, final double whereWhat) {
        return increment(table, what, (double) increment, whereColumn, whereWhat);
    }

    private static String set(final Table table, final Column[] columns, final String[] values) {
        return "UPDATE " + table.getName() + " SET " + joinUpdateClauses(table, columns, values);
    }

    private static String set(final Table table, final Column[] columns, final double[] values) {
        final String[] stringValues = new String[values.length];
        for (int i = 0; i < values.length; i++) {
            stringValues[i] = Double.toString(values[i]);
        }
        return set(table, columns, stringValues);
    }

    private static String set(final Table table, final Column column, final String value) {
        final Column[] columns = {column};
        final String[] values = {value};
        return set(table, columns, values);
    }

    private static String set(final Table table, final Column column, final int value) {
        final Column[] columns = {column};
        final String[] values = {Integer.toString(value)};
        return set(table, columns, values);
    }

    public static String dropTable(final Table table) {
        final String ret = "DROP TABLE " + table.getName();
        history.add(ret);
        return ret;
    }

    public static String deleteAll(final Table table) {
        final String ret = "DELETE FROM " + table.getName();
        history.add(ret);
        return ret;
    }

    public static String delete(final Table table, final Column whereColumn, final String whereValue) {
        return delete(table, new Column[]{whereColumn}, new String[]{whereValue});
    }

    public static String delete(final Table table, final Column[] whereColumns, final String[] whereValues) {
        return deleteAll(table) + " " + where("", table, whereColumns, whereValues);
    }

    public static String deleteWhereId(final Table table, final int id) {
        return delete(table, table.getIdColumn(), Integer.toString(id));
    }

    public static String intersect(final String query, final String query1) {
        final String ret = query + " INTERSECT " + query1;
        history.add(ret);
        return ret;
    }

    public static String trigger(final String name, final boolean after, final Table table, final QueryType qt, boolean forEachRow, final String triggerStatement) {
        final String ret = "CREATE TRIGGER " + name + ((after) ? " AFTER " : " NO CASCADE BEFORE ") + qt + " ON " + table.getName() + " FOR EACH " + ((forEachRow) ? "ROW " : "STATEMENT ") + triggerStatement;
        history.add(ret);
        return ret;
    }

    public static void printHistory() {
        for (String s : history) {
            System.out.println(s + ";");
        }
    }

    public static String updateWhereId(final Table table, final Column what, final Date value, int id) {
        return update(table, what, value, table.getIdColumn(), id);
    }
}
